package com.example.demo.util;

import cn.hutool.core.util.StrUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public class ExcelExportUtil {
    //各个列的表头
    private String[] headList;
    //各个列的元素key值
    private String[] headKey;

    //需要填充的数据信息
    private List<Map> data;

    //默认字体大小
    private int fontSize = 14;
    //工作表名称
    private String sheetName = "xxx数据";


    public String[] getHeadList() {
        return headList;
    }

    public void setHeadList(String[] headList) {
        this.headList = headList;
    }

    public String[] getHeadKey() {
        return headKey;
    }

    public void setHeadKey(String[] headKey) {
        this.headKey = headKey;
    }

    public List<Map> getData() {
        return data;
    }

    public void setData(List<Map> data) {
        this.data = data;
    }

    public int getFontSize() {
        return fontSize;
    }

    public void setFontSize(int fontSize) {
        this.fontSize = fontSize;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }


    /**
     * 2007-2010版本Excel导出
     * @throws IOException
     */
    public void excelExportByXSSF() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建工作表
        XSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        XSSFFont fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        XSSFCellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        XSSFRow row = wbSheet.createRow(0);
        //设置列头元素
        XSSFCell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            XSSFRow roww = wbSheet.createRow(a);
            Map map = data.get(i);
            XSSFCell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            FileOutputStream out = new FileOutputStream("D:\\bigdata\\XSSF.xlsx");
            wb.write(out);
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 2003或以下版本Excel导出
     * @throws IOException
     */
    public void excelExportByHSSF() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工作表
        HSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        HSSFCellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        HSSFRow row = wbSheet.createRow(0);
        //设置列头元素
        HSSFCell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow roww = wbSheet.createRow(a);
            Map map = data.get(i);
            HSSFCell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            FileOutputStream out = new FileOutputStream("D:\\bigdata\\HSSF.xls");
            wb.write(out);
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 百万级数据场景使用
     * @throws IOException
     */
    public void excelExportBySXSSF() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        XSSFWorkbook xssfwb = new XSSFWorkbook();
        //创建工作表
        SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100);
        //创建工作表
        SXSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        CellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        Row row = wbSheet.createRow(0);
        //设置列头元素
        Cell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            Row roww = wbSheet.createRow(a);
            Map map = data.get(i);
            Cell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            FileOutputStream out = new FileOutputStream("D:\\bigdata\\SXSSF.xlsx");
            wb.write(out);
            out.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 检查数据配置问题
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig() throws IOException {
        if (headKey == null || headList.length == 0) {
            throw new IOException("列名数组不能为空或者为空");
        }

        if (fontSize < 0) {
            throw new IOException("字体不能为负值");
        }

        if (StrUtil.isEmpty(sheetName)) {
            throw new IOException("工作表名称不能为空");
        }
    }

    /**
     * 2007-2010版本Excel导出
     * @throws IOException
     */
    public byte[] excelExportByXSSFForWeb() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建工作表
        XSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        XSSFFont fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        XSSFCellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        XSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        XSSFRow row = wbSheet.createRow(0);
        //设置列头元素
        XSSFCell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            XSSFRow roww = wbSheet.createRow(a);
            Map map = data.get(i);
            XSSFCell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            wb.write(out);
            return out.toByteArray();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 2003或以下版本Excel导出
     * @throws IOException
     */
    public byte[] excelExportByHSSFForWeb() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工作表
        HSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        HSSFCellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        HSSFRow row = wbSheet.createRow(0);
        //设置列头元素
        HSSFCell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow roww = wbSheet.createRow(a);
            Map map = data.get(i);
            HSSFCell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            wb.write(out);
            return out.toByteArray();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 百万级数据场景使用
     * @throws IOException
     */
    public byte[] excelExportBySXSSFForWeb() throws IOException {
        //检查参数配置信息
        checkConfig();

        //创建工作簿
        XSSFWorkbook xssfwb = new XSSFWorkbook();
        //创建工作表
        SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100);
        //创建工作表
        SXSSFSheet wbSheet= wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);
        //设置第一列的宽度
        wbSheet.setColumnWidth(0, 20 * 128);

        //表头样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font fontStyle = wb.createFont();
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //字体加粗
        fontStyle.setFontHeightInPoints((short)14);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //数据样式
        CellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        font.setFontName("宋体");//设置字体名称
        style.setFont(font);

        //写入表头数据
        Row row = wbSheet.createRow(0);
        //设置列头元素
        Cell cellHead = null;
        for (int i = 0; i < headList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(headList[i]);
            cellHead.setCellStyle(cellStyle);
        }

        //写入实体数据信息
        int a = 1;
        for (int i = 0; i < data.size(); i++) {
            Row roww = wbSheet.createRow(a);
            Map map = data.get(i);
            Cell cell = null;
            for (int j = 0; j < headKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(headKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(headKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).intValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(StrUtil.isEmpty(value) ? "" : value);
            }
            a++;
        }

        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            wb.write(out);
            return out.toByteArray();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现异常，异常信息：" + ex.getMessage());
        }
    }
}